package com.hlt.shuiliting.database;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 
 * @author Administrator
 *
 */
public class DBUtil {
	Connection conn = null;
	PreparedStatement pstm = null;
	ResultSet rs = null;
	private int pageCount;
	private int updateCount;
	
	/**
	 * 
	 * @param value
	 * @return
	 */
	public String nullToBlank(String value) {
		if(value == null)return "";
		else {
			return value;
		}
	}
	/**
	 * format date string
	 * @return
	 */
	public String formatDateString(String timeString) {
		if(timeString == null || timeString.length() < 10 ) {
			return timeString;
		}
		else {
			return timeString.substring(0,10);
		}
	}
	/**
	 * get connection
	 * @return
	 */
	public Connection getConnection()	 {	
		
		if (conn == null) {
			try {
				Class.forName("oracle.jdbc.driver.OracleDriver");
				conn = DriverManager.getConnection(
						"jdbc:oracle:thin:@localhost:1521:orcl", "mfb", "mfb");
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return conn;
	}
	

	
	public void doPstm(String sql, Object[] params) {
		if (sql != null && !sql.equals("")) {
			if (params == null) {
				params = new Object[0];
			}
			conn = getConnection();
			if (conn != null) {
				try {
					pstm = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
					for(int i=0;i<params.length;i++)
						pstm.setObject(i+1,params[i]);
					pstm.execute();
					updateCount = pstm.getUpdateCount();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} 
			}
		}
	}
	
	@SuppressWarnings("null")
	public List getResultSet() {
		List list = new ArrayList();
		try {
			rs = pstm.getResultSet();
			rs.previous();
			while (rs.next()) {
				Map map = new HashMap();
				ResultSetMetaData rsmd = rs.getMetaData();
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					String key = rsmd.getColumnName(i);
					String value = rs.getString(key);
					//check if database type is date,format string
					String className = rsmd.getColumnClassName(i);
					if(className.toUpperCase().indexOf("TIME") > 0
							|| className.toUpperCase().indexOf("DATE") > 0) {
							value = formatDateString(value);
					}
					value = nullToBlank(value);
					map.put(key, value);
				}			
				list.add(map);	
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	public List getResultSet(int pageSize, int pageNum) {
		List list = new ArrayList();
		try {
			rs = pstm.getResultSet();
			Pageable pageRs = null;
			pageRs = new PageableResultSet2(rs);
			pageRs.setPageSize(pageSize);
			pageCount = pageRs.getPageCount();
			pageRs.gotoPage(pageNum);
			
			for (int i = 0; i < pageRs.getPageRowsCount(); i++) {
				Map map = new HashMap();
				ResultSetMetaData rsmd = pageRs.getMetaData();
				for (int j = 1; j <= rsmd.getColumnCount(); j++) {
					String key = rsmd.getColumnName(j);
					String value = pageRs.getString(key);
					//check if database type is date,format string
					String className = rsmd.getColumnClassName(j);
					if(className.toUpperCase().indexOf("TIME") > 0
							|| className.toUpperCase().indexOf("DATE") > 0) {
							value = formatDateString(value);
					}
					value = nullToBlank(value);
					map.put(key, value);
				}			
				list.add(map);
				pageRs.next();
			}
			/*
			while (pageRs.next()) {
				Map map = new HashMap();
				ResultSetMetaData rsmd = pageRs.getMetaData();
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					String key = rsmd.getColumnName(i);
					String value = pageRs.getString(key);
					//check if database type is date,format string
					//modified by jinhao he,2010--8-12
					String className = rsmd.getColumnClassName(i);
					if(className.toUpperCase().indexOf("TIME") > 0
							|| className.toUpperCase().indexOf("DATE") > 0) {
							value = formatDateString(value);
					}
					value = nullToBlank(value);
					map.put(key, value);
				}			
				list.add(map);	
			}
			*/
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	public int getPageCount() {
		return pageCount;
	}
	
	public int getUpdateCount() {
		return updateCount;
	}
	
	public void closed(){
		try {
			if (rs != null) {
				rs.close();
			} 
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		try{
			if(pstm!=null)
				pstm.close();			
		}catch(SQLException e){
			e.printStackTrace();
		}
		try{
			if(conn!=null){
				conn.close();
			}
		}catch(SQLException e){
			e.printStackTrace();
		}
	}
}
